﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace Hotel
{
    public partial class ChangeRoom : Form
    {
        public ChangeRoom()
        {
            InitializeComponent();
        }
        CNDatabase cnDb = new CNDatabase();
        BindingSource bdSStaying = new BindingSource();
        private void ChangeRoom_Load(object sender, EventArgs e)
        {
            listRoomStaying();
        }
        private void listRoomStaying() {//Cac phong dang o 
            string qr = "select distinct Room.IDRoom ,NameRoom from ListRoomBooking, BookingLog, Customer, Room, TypeRoom "
                    + " where BookingLog.IDBookingLog= ListRoomBooking.IDBookingLog "
                    + " and Customer.IdentificationNumber= BookingLog.IdentificationNumber and Cost = '0' "
                    + " and ListRoomBooking.IDRoom= Room.IDRoom	and Room.IDTypeRoom =TypeRoom.IDTypeRoom and TypeBooking='Staying'";
            bdSStaying.DataSource = cnDb.getDataset(qr).Tables[0];
            cbStaying.DataSource = bdSStaying;
            cbStaying.DisplayMember = "NameRoom";
            cbStaying.ValueMember = "IDRoom";
            
            
            
        }

        private void cbStaying_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                DataRowView row = cbStaying.SelectedItem as DataRowView;
                if (row != null)
                {
                    lbStayingRoom.Text = row["NameRoom"].ToString();
                }

                string qr = " from Customer,BookingLog, ListRoomBooking "
                    + "where Customer.IdentificationNumber=BookingLog.IdentificationNumber "
                    + "and ListRoomBooking.IDBookingLog=BookingLog.IDBookingLog and TotalAmount=0 "
                    + "and TypeBooking='Staying' and ListRoomBooking.IDRoom = '" + cbStaying.SelectedValue + "' ";
                lbName.Text = cnDb.getOneData("select FullName " + qr).ToString();
                lbIdentify.Text = cnDb.getOneData("select BookingLog.IdentificationNumber " + qr).ToString();
                
                string qrStar="select distinct StartDay from ListRoomBooking, BookingLog, Customer, Room, TypeRoom "
                        +" where BookingLog.IDBookingLog= ListRoomBooking.IDBookingLog "
                        +" and Customer.IdentificationNumber= BookingLog.IdentificationNumber and Cost = '0' "
                        +" and ListRoomBooking.IDRoom= Room.IDRoom	and Room.IDTypeRoom =TypeRoom.IDTypeRoom and TypeBooking='Staying'	"
                        +" and Room.IDRoom='"+cbStaying.SelectedValue+"'";
                string qrEnd = "select distinct EndDay from ListRoomBooking, BookingLog, Customer, Room, TypeRoom "
                        + " where BookingLog.IDBookingLog= ListRoomBooking.IDBookingLog "
                        + " and Customer.IdentificationNumber= BookingLog.IdentificationNumber and Cost = '0' "
                        + " and ListRoomBooking.IDRoom= Room.IDRoom	and Room.IDTypeRoom =TypeRoom.IDTypeRoom and TypeBooking='Staying'	"
                        + " and Room.IDRoom='" + cbStaying.SelectedValue + "'";
                txtEndDate.Text = cnDb.getOneData(qrEnd).ToString();
                lbEndDay.Text = cnDb.getOneData(qrEnd).ToString();
                //txtStarday.Text = cnDb.getOneData(qrStar).ToString();
                lbStartDay.Text = cnDb.getOneData(qrStar).ToString();

            }
            catch (Exception) {
                cnDb.closeConnect();
            }
        }

        private void btCheck_Click(object sender, EventArgs e)
        {
            checkVali();
        }



        private void checkVali()
        {
            DateTime starDay = DateTime.Parse(lbStartDay.Text);
            DateTime endDay = DateTime.Parse(txtEndDate.Text);
            DateTime toDay = DateTime.Now;
            //
            if (endDay < starDay)
                MessageBox.Show("No checks in the previous day", "Error", MessageBoxButtons.OK, MessageBoxIcon.Stop);
            else
            {
                cnDb.connect();
                SqlCommand cmd = new SqlCommand("sp_CheckAvailable", cnDb.cn);
                cmd.CommandText = "sp_CheckAvailable";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@StarDay", toDay);
                cmd.Parameters.Add("@EndDay", endDay);             
                SqlDataAdapter adt = new SqlDataAdapter(cmd);             
                DataSet ds= new DataSet();
                adt.Fill(ds);
                BindingSource bdss = new BindingSource();
                bdss.DataSource = ds;
                dataGridView1.DataSource = ds.Tables[0];
                cbRoomVali.DataSource = ds.Tables[0];
               
                cbRoomVali.DisplayMember = "NameRoom";
                cbRoomVali.ValueMember = "IDRoom";
                cnDb.closeConnect();
            }
        }

        private void btChangRoom_Click(object sender, EventArgs e)
        {
            string qrIDList = "select distinct ListRoomBooking.IDListRoom "
                  +"  from ListRoomBooking, BookingLog, Customer, Room, TypeRoom "
                   +" where BookingLog.IDBookingLog= ListRoomBooking.IDBookingLog "
                   +" and Customer.IdentificationNumber= BookingLog.IdentificationNumber and Cost = '0'"
                   +" and ListRoomBooking.IDRoom= Room.IDRoom	and Room.IDTypeRoom =TypeRoom.IDTypeRoom and TypeBooking='Staying' "
                   +" and Room.IDRoom='"+cbStaying.SelectedValue+"'";
            string idList=cnDb.getOneData(qrIDList).ToString();
            // update old room: end date = today
            string qrUpdate = "UPDATE ListRoomBooking SET EndDay = GETDATE() WHERE IDListRoom='"+idList+"'";
            cnDb.xlExecuteNonQuery(qrUpdate);
            //Add NEW ROOM

            string qrIDBooking = "select distinct ListRoomBooking.IDBookingLog "
                 + "  from ListRoomBooking, BookingLog, Customer, Room, TypeRoom "
                  + " where BookingLog.IDBookingLog= ListRoomBooking.IDBookingLog "
                  + " and Customer.IdentificationNumber= BookingLog.IdentificationNumber and Cost = '0'"
                  + " and ListRoomBooking.IDRoom= Room.IDRoom	and Room.IDTypeRoom =TypeRoom.IDTypeRoom and TypeBooking='Staying' "
                  + " and Room.IDRoom='" + cbStaying.SelectedValue + "'";
            string idBooking = cnDb.getOneData(qrIDBooking).ToString();

            string qrInsertListRoom = "INSERT INTO ListRoomBooking(IDBookingLog,IDRoom,StartDay,EndDay,Cost) "
                        +" VALUES('"+idBooking+"', '"+cbRoomVali.SelectedValue+"', GETDATE(), CONVERT(datetime, '"+txtEndDate.Text+"', 103), '0')";
            cnDb.xlExecuteNonQuery(qrInsertListRoom);
            MessageBox.Show("Change Room success");
        }

        private void cbRoomVali_SelectedIndexChanged(object sender, EventArgs e)
        {
            lbCurrentDay.Text = DateTime.Now.ToString("dd/MM/yyyy");
        }

        private void btDayExtend_Click(object sender, EventArgs e)
        {
            txtEndDate.Enabled = true;
        }
      
    }
}
